﻿#region

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;

#endregion

namespace Utils.DB.OleDb
{
    public static class SqlHelperEx
    {
        public static string Connection;

        public static string Path;

        public static int ExecuteNonQuery(string commandText)
        {
            // Pass through the call providing null for the set of OleDbParameters
            return SqlHelper.ExecuteNonQuery(Connection, CommandType.Text, commandText, null);
        }

        public static OleDbDataReader ExecuteReader(string commandText, params OleDbParameter[] commandParameters)
        {
            return SqlHelper.ExecuteReader(Connection, CommandType.Text, commandText, commandParameters);
        }

        public static OleDbDataReader ExecuteReader(string commandText)
        {
            // Pass through the call providing null for the set of OleDbParameters
            return SqlHelper.ExecuteReader(Connection, CommandType.Text, commandText, null);
        }

        public static int ExecuteNonQuery(string commandText, params OleDbParameter[] commandParameters)
        {
            return SqlHelper.ExecuteNonQuery(Connection, CommandType.Text, commandText, commandParameters);
        }

        public static DataSet ExecuteDataSet(string commandText)
        {
            // Pass through the call providing null for the set of OleDbParameters
            return SqlHelper.ExecuteDataSet(Connection, CommandType.Text, commandText, null);
        }

        public static DataSet ExecuteDataSet(string commandText, params OleDbParameter[] commandParameters)
        {
            return SqlHelper.ExecuteDataSet(Connection, CommandType.Text, commandText, commandParameters);
        }

        public static DataTable ExecuteDataTable(string commandText)
        {
            var ds = ExecuteDataSet(commandText);
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            return null;
        }

        public static DataTable ExecuteDataTable(string commandText, params OleDbParameter[] commandParameters)
        {
            var ds = ExecuteDataSet(commandText, commandParameters);
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            return null;
        }

        public static object ExecuteScalar(string commandText)
        {
            // Pass through the call providing null for the set of OleDbParameters
            return SqlHelper.ExecuteScalar(Connection, CommandType.Text, commandText, null);
        }

        public static object ExecuteScalar(string commandText, params OleDbParameter[] commandParameters)
        {
            return SqlHelper.ExecuteScalar(Connection, CommandType.Text, commandText, commandParameters);
        }

//        /// <summary>
//        ///   数据整理
//        /// </summary>
//        /// <param name = "tableName"></param>
//        /// <param name = "items"></param>
//        public static int Redundant(string tableName, List<BaseRelation> items)
//        {
//            var sql = string.Format("delete from [{0}] t where exists (select 1 from [{0}] where * and id>t.id)",
//                                    tableName);
//            var builder = new StringBuilder();
//            foreach (var item in items)
//                if (item.Field != "Id")
//                    builder.Append(string.Format(" and  {0}=t.{0}", item.ColumName));
//            var sqlinside = builder.Remove(0, 4).ToString();
//            sql = sql.Replace("*", sqlinside);
//            return ExecuteNonQuery(sql);
//        }

//        /// <summary>
//        ///   批量添加
//        /// </summary>
//        /// <param name = "importTable"></param>
//        /// <param name = "tableName"></param>
//        /// <param name = "items"></param>
//        public static void Add(DataTable importTable, string tableName, List<BaseRelation> items)
//        {
//            var begin = DateTime.Now;
//            using (var cn = new OleDbConnection(Connection))
//            {
//                cn.Open();
//                var selectCommand = string.Format("select  top 1 * from {0}", tableName); //top 1 
//                var dataAdapter = new OleDbDataAdapter
//                                  {
//                                      SelectCommand = new OleDbCommand(selectCommand, cn)
//                                  };
//
//                #region Insert Command
//
//                var parameters = new List<OleDbParameter>();
//                var baseSql = "insert into [" + tableName + "] ({0}) values ({1})";
//                var builder1 = new StringBuilder();
//                var builder2 = new StringBuilder();
//                foreach (var item in items)
//                {
//                    builder1.Append(item.ColumName);
//                    builder1.Append(",");
//                    builder2.Append(item.ParaName);
//                    builder2.Append(",");
//                    var param = new OleDbParameter(item.ParaName, item.Type, item.Length != null ? item.Length.Value : 0,
//                                                   item.ColumName);
//                    parameters.Add(param);
//                }
//                var sql1 = builder1.ToString().Substring(0, builder1.Length - 1);
//                var sql2 = builder2.ToString().Substring(0, builder2.Length - 1);
//                var sql = string.Format(baseSql, sql1, sql2);
//
//                dataAdapter.InsertCommand = new OleDbCommand(sql, cn);
//                dataAdapter.InsertCommand.Parameters.AddRange(parameters.ToArray());
//
//                #endregion
//
//                var dataset = new DataSet();
//                dataAdapter.Fill(dataset);
//                var table = dataset.Tables[0];
//                //                dataAdapter.UpdateBatchSize = 0;
//                foreach (DataRow importRow in importTable.Rows)
//                {
//                    var row = table.NewRow();
//                    foreach (var item in items)
//                        if (string.IsNullOrEmpty(item.TableColumnField))
//                            row[item.Field] = DBNull.Value;
//                        else
//                            row[item.Field] = importRow[item.TableColumnField];
//                    table.Rows.Add(row);
//                }
//                dataAdapter.Update(table);
//            }
//            var ts = DateTime.Now - begin;
//            Debug.WriteLine(string.Format("ts = {0}", ts.TotalMilliseconds));
//        }

        public static void CompactAccessDB(string connectionString, string mdwfilename)
        {
            object[] oParams;

            //create an inctance of a Jet Replication Object
            var objJRO =
                Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"));

            //filling Parameters array
            //cnahge "Jet OLEDB:Engine Type=5" to an appropriate value
            // or leave it as is if you db is JET4X format (access 2000,2002)
            //(yes, jetengine5 is for JET4X, no misprint here)

            oParams = new object[]
                          {
                              connectionString,
                              "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C://tempdb.mdb;Jet OLEDB:Engine Type=5"
                          };

            //invoke a CompactDatabase method of a JRO object
            //pass Parameters array

            objJRO.GetType().InvokeMember("CompactDatabase",
                                          BindingFlags.InvokeMethod,
                                          null,
                                          objJRO,
                                          oParams);

            //database is compacted now
            //to a new file C://tempdb.mdw
            //let's copy it over an old one and delete it

            File.Delete(mdwfilename);
            File.Move("C://tempdb.mdb", mdwfilename);
            //clean up (just in case)
            Marshal.ReleaseComObject(objJRO);
            objJRO = null;
        }
    }
}